package com.flower.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.flower.domain.entity.FlowerEncyclopedia;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 花卉百科Mapper接口
 */
@Mapper
public interface FlowerEncyclopediaMapper extends BaseMapper<FlowerEncyclopedia> {
    
    /**
     * 查询所有不重复的花卉科
     *
     * @return 花卉科列表
     */
    @Select("SELECT DISTINCT family FROM flower_encyclopedia WHERE family IS NOT NULL AND family != '' AND status = 1")
    List<String> selectFamilyCategories();
    
    /**
     * 根据科查询所有不重复的属
     *
     * @param family 科名
     * @return 属列表
     */
    @Select("SELECT DISTINCT genus FROM flower_encyclopedia WHERE family = #{family} AND genus IS NOT NULL AND genus != '' AND status = 1")
    List<String> selectGeneraByFamily(String family);
    
    /**
     * 获取百科总访问量
     *
     * @return 总访问量
     */
    @Select("SELECT COALESCE(SUM(view_count), 0) FROM flower_encyclopedia")
    Long getTotalViewCount();
    
    /**
     * 根据时间单位获取访问趋势
     *
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @param timeUnit 时间单位
     * @return 访问趋势数据
     */
    List<Map<String, Object>> getViewTrendByTimeUnit(LocalDateTime startDate, LocalDateTime endDate, String timeUnit);
    
    /**
     * 获取花卉科分布
     *
     * @return 科分布数据
     */
    @Select("SELECT family as name, COUNT(*) as value FROM flower_encyclopedia " +
            "WHERE family IS NOT NULL AND family != '' " +
            "GROUP BY family ORDER BY value DESC LIMIT 10")
    List<Map<String, Object>> getFamilyDistribution();
    
    /**
     * 获取花卉属分布
     *
     * @return 属分布数据
     */
    @Select("SELECT genus as name, COUNT(*) as value FROM flower_encyclopedia " +
            "WHERE genus IS NOT NULL AND genus != '' " +
            "GROUP BY genus ORDER BY value DESC LIMIT 10")
    List<Map<String, Object>> getGenusDistribution();
    
    /**
     * 获取花卉难度分布
     *
     * @return 难度分布数据
     */
    @Select("SELECT " +
            "CASE cultivation_difficulty " +
            "WHEN 1 THEN '简单' " +
            "WHEN 2 THEN '中等' " +
            "WHEN 3 THEN '困难' " +
            "WHEN 4 THEN '困难+' " +
            "WHEN 5 THEN '困难++' " +
            "ELSE '未知' END as name, " +
            "COUNT(*) as value " +
            "FROM flower_encyclopedia " +
            "GROUP BY cultivation_difficulty " +
            "ORDER BY cultivation_difficulty")
    List<Map<String, Object>> getDifficultyDistribution();
} 